package com.alianzamedica.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import org.objectsearch.sqlsearch.ObjectSearch;
import org.w3c.dom.Document;

import com.alianzamedica.businessobject.Doctor;
import com.alianzamedica.businessobject.Module;
import com.alianzamedica.connection.ConnectionImpl;
import com.alianzamedica.tools.Enviroment;

/**
 * @author Carlos
 * 
 */
public class ProfileModel {

	/**
	 * @param profileid
	 *            id del perfil del cual se buscan los modulos
	 * @return lista de perfiles relacionados con los modulos
	 * @throws Exception
	 *             si ocurre algun error
	 */
	@SuppressWarnings("unchecked")
	public ArrayList<Module> getModules(Integer profileid) throws Exception {
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ObjectSearch search = new ObjectSearch(doc,
				"com.alianzamedica.connection.ConnectionImpl");
		ArrayList<Module> list = new ArrayList<Module>();
		String sql = "select modules.MODULE_ID,"
				+ "modules.MODULE_NAME,modules.MODULE_URL"
				+ " from modules join module_profile on "
				+ "( modules.module_id = module_profile.module_id ) "
				+ "where module_profile.profile_id = ?";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, profileid);
			list = search.customQuery(psmt, con,
					com.alianzamedica.businessobject.Module.class);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			impl.closeGracefully(psmt);
			impl.close();
		}
		return list;
	}

	/**
	 * @param profileId
	 * @param moduleId
	 * @throws Exception
	 */
	public void addProfileModule(Integer profileId, Integer moduleId)
			throws Exception {
		String sql = "insert into module_profile "
				+ " ( MODULE_ID,PROFILE_ID ) values( ? , ? ) ";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, moduleId);
			psmt.setInt(2, profileId);
			psmt.execute();
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * @param profileId
	 * @param moduleId
	 * @throws Exception
	 */
	public void deleteModule(Integer profileId, Integer moduleId)
			throws Exception {
		String sql = "delete from module_profile "
				+ " where MODULE_ID = ? AND PROFILE_ID = ?";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, moduleId);
			psmt.setInt(2, profileId);
			psmt.execute();
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * regresa lista de modulos no usados
	 * 
	 * @param profileId
	 *            id de perfil
	 * @return array list de modulos no usados
	 * @throws Exception
	 *             si ocurre error
	 */
	@SuppressWarnings("unchecked")
	public ArrayList getUnusedModules(Integer profileId) throws Exception {
		Enviroment env = Enviroment.getInstance();
		Document doc = env.getDocument();
		ObjectSearch search = new ObjectSearch(doc,
				"com.alianzamedica.connection.ConnectionImpl");
		String sql = "select modules.MODULE_ID," + " modules.MODULE_NAME,"
				+ " modules.MODULE_URL from modules "
				+ " where MODULE_ID NOT IN " + " (select modules.MODULE_ID "
				+ " from modules join module_profile "
				+ " on( modules.module_id = module_profile.module_id )"
				+ " where module_profile.profile_id = ?)";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ArrayList list = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, profileId);
			list = search.customQuery(psmt, con, Module.class);
		} finally {
			impl.closeGracefully(psmt);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	/**
	 * checa si la url existe en las restricciones
	 * 
	 * @param url
	 *            url para checar si existe
	 * @return indicador que existe la url en las restricciones
	 * @throws Exception
	 *             si ocurre algun error
	 */
	public boolean urlExists(String url) throws Exception {
		boolean exists = false;
		String sql = "select MODULE_URL from modules where MODULE_URL = ? ";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setString(1, url);
			rs = psmt.executeQuery();
			if (rs.next()) {
				exists = true;
			}
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(rs);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return exists;
	}

	/**
	 * checa si la url existe en las restricciones
	 * 
	 * @param url
	 *            url para checar si existe
	 * @return indicador que existe la url en las restricciones
	 * @throws Exception
	 *             si ocurre algun error
	 */
	public boolean userIsLoggingIn(String url) throws Exception {
		boolean exists = false;
		String sql = "select MODULE_NAME from modules where MODULE_URL = ? ";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setString(1, url);
			rs = psmt.executeQuery();
			if (rs.next()) {
				String module = rs.getString(1);
				if (module.equals("Principal")||module.equals("Login")) {
					exists = true;
				}
			}
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(rs);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return exists;
	}

	/**
	 * comprueba si el doctor tiene acceso a una url.
	 * 
	 * @param doctor
	 *            medico logueado en el sistema.
	 * @param url
	 *            este es el url que se ha accesado en el sistema.
	 * @return regresa validacion si la entrada es satisfactoria.
	 * @throws Exception
	 *             si ocurre error.
	 */
	public boolean hasAccess(Doctor doctor, String url) throws Exception {
		String sql = "select * from module_profile where profile_id = "
				+ " (select DOCTOR_PROFILE from doctor where doctor_id = ? )"
				+ "and module_id = "
				+ "(select module_id from modules where module_url = ? )";
		ConnectionImpl impl = new ConnectionImpl();
		PreparedStatement psmt = null;
		ResultSet rs = null;
		boolean access = false;
		try {
			impl.open();
			Connection con = impl.getConnection();
			psmt = con.prepareStatement(sql);
			psmt.setInt(1, doctor.getId());
			psmt.setString(2, url);
			rs = psmt.executeQuery();
			while (rs.next()) {
				access = true;
			}
		} finally {
			impl.closeGracefully(psmt);
			impl.closeGracefully(rs);
			try {
				impl.close();
			} catch (Exception e) {
			}
		}
		return access;
	}

}
